package com.ops.repository;

import com.ops.entity.OpsTask;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

/**
 * 运维任务数据访问接口
 */
public interface OpsTaskRepository extends JpaRepository<OpsTask, Long> {

    /**
     * 根据报告UUID、医院ID、需求年份和需求ID查询单个运维任务
     * @param reportUuid 报告UUID
     * @param hospitalId 医院ID
     * @param demandYear 需求年份
     * @param demandId 需求ID
     * @return 运维任务
     */
    Optional<OpsTask> findByReportUuidAndHospitalIdAndDemandYearAndDemandId(
            String reportUuid,
            String hospitalId,
            String demandYear,
            String demandId
    );

    /**
     * 根据报告UUID、医院ID和需求年份查询运维任务列表，按实际工作量降序排序
     * @param reportUuid 报告UUID
     * @param hospitalId 医院ID
     * @param demandYear 需求年份
     * @param pageable 分页参数
     * @return 分页的运维任务列表
     */
    @Query("SELECT t FROM OpsTask t WHERE t.reportUuid = :reportUuid AND t.hospitalId = :hospitalId AND t.hospitalId = :hospitalId AND t.demandYear = :demandYear " +
           "ORDER BY t.actualWorkload DESC")
    Page<OpsTask> findTopTasksByHospitalAndYear(
        @Param("reportUuid") String reportUuid,
        @Param("hospitalId") String hospitalId,
        @Param("demandYear") String demandYear,
        Pageable pageable
    );
    
    /**
     * 获取每月需求统计数据
     * @param reportUuid 报告UUID
     * @return 包含月份和需求数量的统计数据列表
     */
    @Query("SELECT MONTH(t.demandDate) as month, COUNT(t) as count FROM OpsTask t " +
           "WHERE t.reportUuid = :reportUuid " +
           "GROUP BY MONTH(t.demandDate) " +
           "ORDER BY month")
    List<Object[]> getMonthlyDemandStats(
        @Param("reportUuid") String reportUuid
    );

    /**
     * 获取每月工作量统计数据
     * @param reportUuid 报告UUID
     * @return 包含月份和总工作量的统计数据列表
     */
    @Query("SELECT MONTH(t.demandDate) as month, SUM(t.actualWorkload) as totalWorkload FROM OpsTask t " +
           "WHERE t.reportUuid = :reportUuid " +
           "GROUP BY MONTH(t.demandDate) " +
           "ORDER BY month")
    List<Object[]> getMonthlyWorkloadStats(
        @Param("reportUuid") String reportUuid
    );

    /**
     * 根据报告UUID查询所有运维任务
     * @param reportUuid 报告UUID
     * @return 运维任务列表
     */
    List<OpsTask> findByReportUuid(String reportUuid);

    /**
     * 根据报告UUID删除运维任务
     * @param reportUuid 报告UUID
     */
    void deleteByReportUuid(String reportUuid);
}